package org.kl.auditsupervision.repository.fnd;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.kl.auditsupervision.dto.audit.CompletionRateStatistic;
import org.kl.auditsupervision.dto.audit.RiskDimensionStatistic;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

@Component
public class CompletionRateStatisticDao {
	@Autowired
	JdbcTemplate jdbcTemplate;

	public List<CompletionRateStatistic> getCompletionRateStatistic(String startYear,String endYear,String type,String orgIds,
			int pageNumber, int pageSize, String sortType, String orderType) {
		final List<CompletionRateStatistic> result = new ArrayList<>();
		// oracle的SQL语句
		StringBuilder sql = new StringBuilder();
		
		String sqlString="";
		if(StringUtils.isNoneBlank(orgIds)){
			String[] orgArray=orgIds.split(",");
			for(int i=0;i<orgArray.length;i++){
				if(i!=orgArray.length-1){
					sqlString+=getSql(startYear,endYear,type,orgArray[i], sortType, orderType)+ " union all ";
				}else{
					sqlString+=getSql(startYear,endYear,type,orgArray[i], sortType, orderType);
				}
			}
		}else{
			sqlString+=getSql(startYear,endYear,type,orgIds, sortType, orderType);
		}
		if(pageNumber==0&&pageSize==0){
			//excel不分页
		}else{
			sqlString=getPaginationSql(sqlString,pageNumber,pageSize);
		}
		sql.append(sqlString);

		this.jdbcTemplate.query(sql.toString(), new RowCallbackHandler() {
			int i = 0;

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				CompletionRateStatistic completionRateStatistic = new CompletionRateStatistic();
				completionRateStatistic.setProjectName(rs.getString("AUDIT_NAME"));
				completionRateStatistic.setOrgName(rs.getString("org_name"));
				completionRateStatistic.setZgwcl(rs.getString("zgwcl"));
				completionRateStatistic.setZgjhastjl(rs.getString("zgjhastjl"));
				completionRateStatistic.setZgjhyctgl(rs.getString("zgjhyctjl"));
				completionRateStatistic.setZgcgastjl(rs.getString("zgcgastjl"));
				completionRateStatistic.setZzzgjgyctgl(rs.getString("zzcgyctgl"));
				completionRateStatistic.setSjwtdqzgl(rs.getString("sjwtdqzgl"));
				i++;
				completionRateStatistic.setDisplaySeq(Integer.parseInt(i + "0"));
				result.add(completionRateStatistic);
			}
		});
		return result;
	}

	private String getPaginationSql(String sql, int pageNumber, int pageSize) {
		String startRow = String.valueOf(pageSize * (pageNumber-1));
		String endRow = String.valueOf(pageSize * pageNumber);
		String paginationSql = "";
		paginationSql = "select * from (select row_.*, rownum rownum_ from ("
				+ sql + ")row_ where rownum <= " + endRow
				+ ")where rownum_ > " + startRow;
		return paginationSql;
	}
	
	private String getSql(String startDate,String endDate,String type,String orgId, String sortType, String orderType){
		String sqlString = "";
	
		sqlString += "   select a.AUDIT_NAME,";
		if(StringUtils.isNotBlank(orgId)){
			sqlString += " o.org_name,";
		}else{
			sqlString += "'' as org_name,";
		}
		sqlString +=  "round(sum(decode(confirm_flag, '1', 1,0)) / count(*)*100,2)||'%' as zgwcl,"+
                " round(sum(dECODE(dtl.zg_plan_sub_sj_time ,null,0,dECODE(sign(dtl.zg_plan_sub_time -"+
                "                dtl.zg_plan_sub_sj_time),"+
                "           1,"+
                "           1,0))) / count(*) * 100,"+
                " 2)||'%' as zgjhastjl,"+
                  " round(sum(decode(plan_times, 0,1,0)) / count(*) * 100, 2)||'%' as zgjhyctjl, "+
                  " round(sum(decode(sign(dtl.zg_result_sub_time - "+
                  "                        dtl.zg_result_sub_sj_time), "+
                  "                   1, "+
                  "                   1,0)) / count(*) * 100, "+
                  "      2)||'%' as zgcgastjl, "+
                  " round(sum(decode(dtl.result_times, 0,1,0)) / count(*) * 100, 2)||'%' as zzcgyctgl,"+
                  "decode(sum(decode(sign(to_date('"+endDate+" 23:59:59',"+
                  "        'YYYY-MM-DD HH24:mi:ss') -"+
                  "dtl.zg_result_sub_sj_time),"+
             "1,"+
             "1,"+
             "0)) + sum(decode(sign(dtl.zg_result_sub_time -"+
             "                      to_date('2018-06-30 23:59:59',"+
             "                              'YYYY-MM-DD HH24:mi:ss')),"+
             "                 1,"+
             "                 decode(sign(to_date('2018-06-30 23:59:59',"+
             "                                     'YYYY-MM-DD HH24:mi:ss') -"+
             "                             dtl.zg_result_sub_sj_time),"+
             "                        1,"+
             "                        1,"+
             "                        0),"+
             "                 0)),0,0,("+
                   "  round(sum(decode(sign(to_date('"+endDate+" 23:59:59','YYYY-MM-DD HH24:mi:ss') -"+
                   "             dtl.zg_result_sub_sj_time),"+
                   "        1,"+
                   "        1,0)) / (sum(decode(sign(to_date('"+endDate+" 23:59:59',"+
                   "                  'YYYY-MM-DD HH24:mi:ss') -"+
                   "          dtl.zg_result_sub_sj_time),"+
                   "     1,"+
                   "     1,"+
                   "     0))+sum(decode(sign(dtl.zg_result_sub_time-to_date('"+endDate+" 23:59:59',"+
                   "                  'YYYY-MM-DD HH24:mi:ss') "+
                   "          ),"+
                   "     1,"+
                   "     decode(sign(to_date('"+endDate+" 23:59:59',"+
                   "                  'YYYY-MM-DD HH24:mi:ss') -"+
                   "          dtl.zg_result_sub_sj_time),"+
                   "     1,"+
                   "     1,"+
                   "     0),"+
                   "     0))) * 100,"+
              "2)))||'%' as sjwtdqzgl "+
    "        from sjjc_audit_details dtl"+
    "       left join sjjc_audit a on a.id = dtl.AUDIT_PRO_ID"+
    " left join fnd_org o on o.id = dtl.org_id where 1=1";
  		  if(StringUtils.isNotBlank(startDate)){
  			  sqlString+=" and  dtl.create_time>=to_date('"+startDate+"','YYYY-MM-DD') ";
  		  }
  		  if(StringUtils.isNotBlank(endDate)){
  			  sqlString+=" and  dtl.create_time<=to_date('"+endDate+"','YYYY-MM-DD') ";
  		  }
  		  if(StringUtils.isNotBlank(type)){
  			  sqlString+=" and a.type = '"+type+"' ";
  		  }
  		if(StringUtils.isNotBlank(orgId)){
			  sqlString+=" and dtl.org_id = '"+orgId+"' group by a.AUDIT_NAME,o.org_name ";
		  }else{
			  sqlString+="       group by a.AUDIT_NAME ";
		  }
		return sqlString;
	}
	
	public int getCompletionRateStatisticCount(String startYear,String endYear,String type,String orgIds) {

		// oracle的SQL语句
		StringBuilder sql = new StringBuilder();
		
		String sqlString="";
		if(StringUtils.isNoneBlank(orgIds)){
			String[] orgArray=orgIds.split(",");
			for(int i=0;i<orgArray.length;i++){
				if(i!=orgArray.length-1){
					sqlString+=getSql(startYear,endYear,type,orgArray[i], "", "")+ " union all ";
				}else{
					sqlString+=getSql(startYear,endYear,type,orgArray[i], "", "");
				}
			}
		}else{
			sqlString+=getSql(startYear,endYear,type,orgIds, "", "");
		}

		sql.append("select count(1) as count from ("+sqlString+")");
		
		Integer count=(Integer)this.jdbcTemplate.queryForObject(sql.toString(),Integer.class);
		
		int coungInt=count.intValue();

		return coungInt;
	}

}
